﻿Imports System.Data.SqlClient

Public Class frmRenewalsFollowUp

    Sub FillRenewal()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Renewal Where Status=N'Active' Order By ExpiryDate", cnn)
            Dim Reader As SqlDataReader
            Dim AssignDate As String

            Me.GridRenewals.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("AssignedDate").ToString = "1/1/1900 12:00:00 AM" Then
                    AssignDate = ""
                Else
                    AssignDate = CDate(Reader.Item("AssignedDate")).ToString("yyyy/MM/dd")
                End If

                Me.GridRenewals.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("Status"), Reader.Item("ClientID"), Reader.Item("ClientName"), _
                                                       Reader.Item("InsuranceCompany"), Reader.Item("PolicyNo"), _
                                                       Reader.Item("ClassofBusiness"), CDate(Reader.Item("IssueDate")).ToString("yyyy/MM/dd"), _
                                                       CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd"), _
                                                       Reader.Item("AssignedTo"), Reader.Item("AssignedBy"), AssignDate})
            End While
            cnn.Close()

            For Each row As DataGridViewRow In Me.GridRenewals.Rows
                row.Cells(1).Style.BackColor = Color.Yellow
            Next

              'Check Expiry Date
            For Each Row As DataGridViewRow In Me.GridRenewals.Rows
                If Now.Date >= CType(Row.Cells(8).Value, Date).Date Then
                    Row.Cells(8).Style.BackColor = Color.Red
                Else
                    Row.Cells(8).Style.BackColor = Color.White
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmRenewalsFollowUp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillRenewal()
        FillUsersList()
    End Sub

    Sub FillUsersList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select FullName From Users Order By FullName", cnn)
            Dim Reader As SqlDataReader

            Me.ChListContacts.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.ChListContacts.Items.Add(Reader.Item("FullName"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridRenewals_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GridRenewals.CurrentCellChanged
        Try
            Me.Cursor = Cursors.WaitCursor

            If Me.GridRenewals.CurrentRow Is Nothing Then
                Me.Cursor = Cursors.Default
            Else
                Me.txtProspectNo.Text = Me.GridRenewals.CurrentRow.Cells(5).Value
                FillRenewalNotes()
            End If

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillRenewalNotes()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From RenewalNotes Where RNo=" & Me.GridRenewals.CurrentRow.Cells(0).Value & "Order By SNo", cnn1)
            Dim Reader As SqlDataReader

            Me.txtProgressNotes.Clear()

            Dim ListHeader, ListNotes As New ArrayList

            cnn1.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.txtProgressNotes.Text += Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd hh:mm:ss tt") & _
                                        Chr(13) & Reader.Item("Notes") & Chr(13) & Chr(13)

                ListHeader.Add(Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd hh:mm:ss tt"))
                ListNotes.Add(Reader.Item("Notes"))
            End While
            cnn1.Close()

            For Each T As String In ListHeader
                txtProgressNotes.SelectionStart = txtProgressNotes.Find(T)
                Dim bfont As New Font(txtProgressNotes.Font, FontStyle.Bold)
                txtProgressNotes.SelectionFont = bfont
                txtProgressNotes.SelectionColor = Color.Blue
            Next

            Me.txtProgressNotes.ScrollToCaret()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        SaveNotes()
    End Sub

    Sub SaveNotes()
        If Me.txtNotes.Text.Trim.Length = 0 Then
            MsgBox("Please enter your message")
            Me.txtNotes.Clear()
            Me.txtNotes.Focus()
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Insert Into RenewalNotes (RNo,Notes,SavedUser) Values " & _
                                          "(@RNo,@Notes,@SavedUser)", cnn)
                cnn.Open()

                'Add values
                cmd.Parameters.AddWithValue("@RNo", Me.GridRenewals.CurrentRow.Cells(0).Value)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text.Trim)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                cmd.ExecuteNonQuery()
                cnn.Close()

                'Send notification
                Dim Subject, Body As String
                Dim Receivers As New ArrayList

                If Me.ChListContacts.CheckedItems.Count > 0 Then
                    Subject = "Oasis - IBS Notification - Sales Management System"
                    Body = "Your received a notification from: <B>" & CurrentUser & _
                           ", regarding renewals.</B><br/><br/>" & _
                           "Please don't reply to this email.<br/>" & _
                           "Sent from Oasis - Insurance Broker System"
                    For i As Integer = 0 To Me.ChListContacts.Items.Count - 1
                        If Me.ChListContacts.GetItemChecked(i) Then
                            Receivers.Add(GetUserEmail(Me.ChListContacts.Items(i)))
                        End If
                    Next

                    SendEmail(Subject, Body, Receivers)

                    MsgBox("Notification has been sent successfully")
                End If

                FillRenewalNotes()

                Me.txtNotes.Clear()
                Me.txtNotes.Focus()
                For i As Integer = 0 To Me.ChListContacts.Items.Count - 1
                    Me.ChListContacts.SetItemChecked(i, False)
                Next

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class